package com.fan.dao.bill;

import com.fan.dao.BaseDao;
import com.fan.pojo.Bill;
import com.fan.pojo.Provider;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BillDaoImpl implements BillDao{

    @Override
    public int getBillCount(Connection connection, String productName, int providerId, int isPayment) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;
        if (connection!=null){
            StringBuffer sql = new StringBuffer();
            List<Object> list = new ArrayList<>();
            sql.append("select count(1) as count from smbms_bill b,smbms_provider p where b.providerId = p.id");
            if (!StringUtils.isNullOrEmpty(productName)) {
                sql.append(" and productName like ?");
                list.add("%" + productName + "%");
            }
            if (providerId > 0) {
                sql.append(" and providerId = ?");
                list.add(providerId);
            }
            if (isPayment > 0) {
                sql.append(" and isPayment = ?");
                list.add(isPayment);
            }
            Object[] params = list.toArray();
            rs = BaseDao.execute(sql.toString(), params, connection, pstm, rs);
            if (rs.next()){
                count = rs.getInt("count");
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }

    @Override
    public List<Bill> getBillList(Connection connection, String productName, int providerId, int isPayment, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Bill> billList = new ArrayList<>();
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            List<Object> list = new ArrayList<>();
            sql.append("select * from smbms_bill b,smbms_provider p where b.providerId=p.id");
            if (!StringUtils.isNullOrEmpty(productName)) {
                sql.append(" and productName like ?");
                list.add("%" + productName + "%");
            }
            if (providerId > 0) {
                sql.append(" and providerId = ?");
                list.add(providerId);
            }
            if (isPayment > 0) {
                sql.append(" and isPayment = ?");
                list.add(isPayment);
            }
            sql.append(" order by b.creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            rs = BaseDao.execute(sql.toString(),params,connection,pstm,rs);
            while (rs.next()){
                Bill _bill = new Bill();
                _bill.setId(rs.getInt("id"));
                _bill.setBillCode(rs.getString("billCode"));
                _bill.setProductName(rs.getString("productName"));
                _bill.setProductDesc(rs.getString("productDesc"));
                _bill.setProductUnit(rs.getString("productUnit"));
                _bill.setProductCount(rs.getBigDecimal("productCount"));
                _bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                _bill.setIsPayment(rs.getInt("isPayment"));
                _bill.setCreatedBy(rs.getInt("createdBy"));
                _bill.setCreationDate(rs.getDate("creationDate"));
                _bill.setModifyBy(rs.getInt("modifyBy"));
                _bill.setModifyDate(rs.getDate("modifyDate"));
                _bill.setProviderId(rs.getInt("providerId"));
                _bill.setProviderName(rs.getString("proName"));
                billList.add(_bill);
            }
            BaseDao.closeResource(null,pstm,rs);

        }
        return billList;
    }

    @Override
    public int addBill(Connection connection, Bill bill) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null){
            String sql = "insert into smbms_bill (billCode,productName,productDesc,productUnit,productCount,totalPrice,isPayment,createdBy,creationDate,providerId) values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {bill.getBillCode(),bill.getProductName(),bill.getProductDesc(),bill.getProductUnit(),bill.getProductCount(),bill.getTotalPrice(),bill.getIsPayment(),bill.getCreatedBy(),bill.getCreationDate(),bill.getProviderId()};
            updateRows = BaseDao.execute(sql,params,connection,pstm);
            BaseDao.closeResource(null,pstm,null);
        }
        return updateRows;
    }

    @Override
    public int deleteBillById(Connection connection, Integer delId) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null){
            String sql = "delete from smbms_bill where id=?";
            Object[] params = {delId};
            updateRows = BaseDao.execute(sql, params, connection, pstm);
            BaseDao.closeResource(null,pstm,null);
        }
        return updateRows;
    }

    @Override
    public Bill getBillById(Connection connection, String id) throws SQLException {
        Bill bill = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        if (connection != null){
            String sql = "select * from smbms_bill b,smbms_provider p where b.id=? and b.providerId=p.id";
            Object[] params = {id};
            rs = BaseDao.execute(sql,params,connection,pstm,rs);
            if (rs.next()){
                bill = new Bill();
                bill.setId(rs.getInt("b.id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setProviderName(rs.getString("p.proName"));
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return bill;
    }

    @Override
    public boolean updateBillById(Connection connection, String id, String billCode, String productName, String productDesc, String productUnit, String productCount, String totalPrice, String isPayment, String providerId) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (connection != null){
            String sql = "update smbms_bill set billCode=?,productName=?,productDesc=?,productUnit=?,productCount=?,totalPrice=?,isPayment=?,providerId=? where id = ?";
            Object[] params = {billCode,productName,productDesc,productUnit,productCount,totalPrice,isPayment,providerId,id};
            updateRows = BaseDao.execute(sql,params,connection,pstm);
            return true;
        }else {
            return false;
        }
    }
}
